USE master;

--IF EXISTS (SELECT name FROM sys.databases WHERE name = N'sp_db')
--DROP DATABASE [sp_db];


IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'sp_db')
CREATE DATABASE sp_db;

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'sp_db')
USE sp_db;

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[s_empty]') AND type in (N'U'))
DROP TABLE s_empty;

CREATE TABLE s_empty (
  S_NO varchar(20) NOT NULL,
  SNAME varchar(500) NOT NULL,
  STATUS int NOT NULL,
  CITY varchar(20) NOT NULL,
  PRIMARY KEY  (S_NO)
);

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[s]') AND type in (N'U'))
DROP TABLE s;

CREATE TABLE s (
  S_NO varchar(20) NOT NULL,
  SNAME varchar(500) NOT NULL,
  STATUS int NOT NULL,
  CITY varchar(20) NOT NULL,
  PRIMARY KEY  (S_NO)
)

INSERT INTO s
SELECT 'S1' AS 'S_NO', 'Smith' AS 'SNAME', 20 AS 'STATUS','London' AS 'CITY' UNION
SELECT 'S2', 'Jones', 10, 'Paris' UNION
SELECT 'S3', 'Blake', 30, 'Paris' UNION
SELECT 'S4', 'Clark', 20, 'London' UNION
SELECT 'S5', 'Adams', 30, 'Athens';

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[p]') AND type in (N'U'))
DROP TABLE p;

CREATE TABLE p (
  P_NO varchar(20) NOT NULL,
  PNAME varchar(500) NOT NULL,
  COLOR varchar(20) NOT NULL,
  WEIGHT decimal(11, 2) NOT NULL,
  CITY varchar(20) NOT NULL,
  PRIMARY KEY  (P_NO)
);

INSERT INTO p
SELECT 'P1' AS 'P_NO', 'Nut' AS 'PNAME', 'Red' AS 'COLOR', 12.0 AS 'WEIGHT', 'London' AS 'CITY' UNION
SELECT 'P2', 'Bolt', 'Green', 17.0, 'Paris' UNION
SELECT 'P3', 'Screw', 'Blue', 17.0, 'Oslo' UNION
SELECT 'P4', 'Screw', 'Red', 14.0, 'London' UNION
SELECT 'P5', 'Cam', 'Blue', 12.0, 'Paris' UNION
SELECT 'P6', 'Cog', 'Red', 19.0, 'London';

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[p_details]') AND type in (N'U'))
DROP TABLE p_details;

CREATE TABLE p_details (
  P_NO varchar(20) NOT NULL,
  DESCRIPTION VARCHAR(MAX) NOT NULL,
  LAST_EDITED DATETIME NOT NULL,
  DATE_ADDED DATETIME NOT NULL
  PRIMARY KEY  (P_NO)
);

INSERT INTO p_details (P_NO, DESCRIPTION, LAST_EDITED, DATE_ADDED)
SELECT 'P2' AS 'P_NO', '<p>A <b>screw</b>, or <b>bolt</b>, is a type of <a href=''''/wiki/Fastener''''>fastener</a> characterized by a <a href=''''/wiki/Helix'''' title=''''Helix''''>helical</a> ridge, known as an <i>external thread</i> or just <i><a href=''''/wiki/Screw_thread'''' title=''''Screw thread''''>thread</a></i>, wrapped around a cylinder. Some screw threads are designed to mate with a complementary thread, known as an <i>internal thread</i>, often in the form of a <a href=''''/wiki/Nut_(hardware)'''' title=''''Nut (hardware)''''>nut</a> or an object that has the internal thread formed into it. Other screw threads are designed to cut a helical groove in a softer material as the screw is inserted. The most common uses of screws are to hold objects together and to position objects.</p>' AS 'DESCRIPTION', '2011-02-19' AS 'LAST_EDITED', '2011-02-19' AS 'DATE_ADDED' UNION
SELECT 'P3', '<p>A <b>screw</b>, or <b>bolt</b>, is a type of <a href=''''/wiki/Fastener''''>fastener</a> characterized by a <a href=''''/wiki/Helix'''' title=''''Helix''''>helical</a> ridge, known as an <i>external thread</i> or just <i><a href=''''/wiki/Screw_thread'''' title=''''Screw thread''''>thread</a></i>, wrapped around a cylinder. Some screw threads are designed to mate with a complementary thread, known as an <i>internal thread</i>, often in the form of a <a href=''''/wiki/Nut_(hardware)'''' title=''''Nut (hardware)''''>nut</a> or an object that has the internal thread formed into it. Other screw threads are designed to cut a helical groove in a softer material as the screw is inserted. The most common uses of screws are to hold objects together and to position objects.</p>', '2011-02-19', '2011-02-19' UNION
SELECT 'P4', '<p>A <b>screw</b>, or <b>bolt</b>, is a type of <a href=''''/wiki/Fastener''''>fastener</a> characterized by a <a href=''''/wiki/Helix'''' title=''''Helix''''>helical</a> ridge, known as an <i>external thread</i> or just <i><a href=''''/wiki/Screw_thread'''' title=''''Screw thread''''>thread</a></i>, wrapped around a cylinder. Some screw threads are designed to mate with a complementary thread, known as an <i>internal thread</i>, often in the form of a <a href=''''/wiki/Nut_(hardware)'''' title=''''Nut (hardware)''''>nut</a> or an object that has the internal thread formed into it. Other screw threads are designed to cut a helical groove in a softer material as the screw is inserted. The most common uses of screws are to hold objects together and to position objects.</p>', '2011-02-19', '2011-02-19' UNION
SELECT 'P5', '<p>A <b>cam</b> is a rotating or sliding piece in a mechanical <a href=''''/wiki/Linkage''''>linkage</a> used especially in transforming rotary motion into linear motion or vice-versa.<sup id=''''cite_ref-0'''' class=''''reference''''><a href=''''#cite_note-0''''><span>[</span>1<span>]</span></a></sup><sup id=''''cite_ref-1'''' class=''''reference''''><a href=''''#cite_note-1''''><span>[</span>2<span>]</span></a></sup> It is often a part of a rotating <a href=''''/wiki/Wheel''''>wheel</a> (e.g. an eccentric wheel) or shaft (e.g. a cylinder with an irregular shape) that strikes a <a href=''''/wiki/Lever''''>lever</a> at one or more points on its circular path. The cam can be a simple tooth, as is used to deliver pulses of power to a <a href=''''/wiki/Steam_hammer''''>steam hammer</a>, for example, or an <a href=''''/wiki/Eccentric_(mechanism)'''' title=''''Eccentric (mechanism)''''>eccentric</a> disc or other shape that produces a smooth reciprocating (back and forth) motion in the <i><a href=''''/wiki/Cam_follower'''' title=''''Cam follower''''>follower</a></i>, which is a lever making contact with the cam.</p>', '2011-02-19', '2011-02-19' UNION
SELECT 'P6', '<p>A <b>gear</b>, or cog, is a <a href=''''/wiki/Rotating'''' class=''''mw-redirect'''' title=''''Rotating''''>rotating</a> <a href=''''/wiki/Machine''''>machine</a> part having cut <i>teeth</i>, or <i>cogs</i>, which <i>mesh</i> with another toothed part in order to transmit <a href=''''/wiki/Torque''''>torque</a>. Two or more gears working in tandem are called a <i><a href=''''/wiki/Transmission_(mechanics)'''' title=''''Transmission (mechanics)''''>transmission</a></i> and can produce a <a href=''''/wiki/Mechanical_advantage''''>mechanical advantage</a> through a <a href=''''/wiki/Gear_ratio''''>gear ratio</a> and thus may be considered a <a href=''''/wiki/Simple_machine''''>simple machine</a>. Geared devices can change the speed, magnitude, and direction of a <a href=''''/wiki/Power_(physics)'''' title=''''Power (physics)''''>power source</a>. The most common situation is for a gear to mesh with another gear, however a gear can also mesh a non-rotating toothed part, called a rack, thereby producing <a href=''''/wiki/Translation_(physics)'''' title=''''Translation (physics)''''>translation</a> instead of rotation.</p> \n<p>The gears in a transmission are analogous to the <a href=''''/wiki/Wheel'''' title=''''Wheel''''>wheels</a> in a <a href=''''/wiki/Pulley''''>pulley</a>. An advantage of gears is that the teeth of a gear prevent slipping.</p>', '2011-02-19', '2011-02-19';

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp]') AND type in (N'U'))
DROP TABLE sp;

CREATE TABLE sp (
  S_NO varchar(20) NOT NULL,
  P_NO varchar(20)  NOT NULL,
  QTY int NOT NULL
);

INSERT INTO sp
SELECT 'S1' AS 'S_NO', 'P1' AS 'P_NO', 300 AS 'QTY' UNION
SELECT 'S1', 'P2', 200 UNION 
SELECT 'S1', 'P3', 400 UNION
SELECT 'S1', 'P4', 200 UNION
SELECT 'S1', 'P5', 100 UNION
SELECT 'S1', 'P6', 100 UNION
SELECT 'S2', 'P1', 300 UNION
SELECT 'S2', 'P2', 400 UNION
SELECT 'S3', 'P2', 200 UNION
SELECT 'S4', 'P2', 200 UNION
SELECT 'S4', 'P4', 300 UNION
SELECT 'S4', 'P5', 400

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_with_cancelled]') AND type in (N'U'))
DROP TABLE sp_with_cancelled;

CREATE TABLE sp_with_cancelled (
  S_NO varchar(20)  NOT NULL,
  P_NO varchar(20)  NOT NULL,
  QTY int  NOT NULL,
  IS_CANCELLED BIT
);

INSERT INTO sp_with_cancelled
SELECT 'S1' AS 'S_NO', 'P1' AS 'P_NO', 300 AS 'QTY', 1 AS 'IS_CANCELLED' UNION
SELECT 'S1', 'P2', 200, 0 UNION
SELECT 'S1', 'P3', 400, 0 UNION
SELECT 'S1', 'P4', 200, 1 UNION
SELECT 'S1', 'P5', 100, 1 UNION
SELECT 'S1', 'P6', 100, 1 UNION
SELECT 'S2', 'P1', 300, 1 UNION
SELECT 'S2', 'P2', 400, 0 UNION
SELECT 'S3', 'P2', 200, 1 UNION
SELECT 'S4', 'P2', 200, 0 UNION
SELECT 'S4', 'P4', 300, 0 UNION
SELECT 'S4', 'P5', 400, 1;

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[s_relations]') AND type in (N'U'))
DROP TABLE s_relations;

CREATE TABLE s_relations (
  PARENT_SUPPLIER varchar(500) NOT NULL,
  CHILD_SUPPLIER varchar(500) NOT NULL
);

INSERT INTO s_relations
SELECT 'S1' AS 'PARENT_SUPPLIER', 'S4' AS 'CHILD_SUPPLIER' UNION
SELECT 'S4', 'S5';

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[part_structure]') AND type in (N'U'))
DROP TABLE part_structure;

CREATE TABLE part_structure (
  MAJOR_P_NO varchar(20) NOT NULL,
  MINOR_P_NO varchar(20) NOT NULL,
  QTY INT NOT NULL
 CONSTRAINT [PK_part_structure] PRIMARY KEY CLUSTERED 
(
	[MAJOR_P_NO] ASC,
	[MINOR_P_NO] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];

INSERT INTO part_structure
SELECT 'P1' AS 'MAJOR_P_NO', 'P2' AS 'MINOR_P_NO', 2 AS 'QTY' UNION
SELECT 'P1' AS 'MAJOR_P_NO', 'P3' AS 'MINOR_P_NO', 4 AS 'QTY' UNION
SELECT 'P2' AS 'MAJOR_P_NO', 'P3' AS 'MINOR_P_NO', 1 AS 'QTY' UNION
SELECT 'P2' AS 'MAJOR_P_NO', 'P4' AS 'MINOR_P_NO', 3 AS 'QTY' UNION
SELECT 'P3' AS 'MAJOR_P_NO', 'P5' AS 'MINOR_P_NO', 9 AS 'QTY' UNION
SELECT 'P4' AS 'MAJOR_P_NO', 'P5' AS 'MINOR_P_NO', 8 AS 'QTY' UNION
SELECT 'P5' AS 'MAJOR_P_NO', 'P6' AS 'MINOR_P_NO', 3 AS 'QTY';


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[p_with_re_types]') AND type in (N'U'))
DROP TABLE p_with_re_types;

CREATE TABLE p_with_re_types (
  P_NO varchar(20) NOT NULL,
  ITEM_COUNT$re_Test_Integer INT NOT NULL,
  LOCATION$re_Test_Point VARCHAR(1000) NOT NULL,
  [S${"Relation":{"S":"string"}}] VARCHAR(MAX) NOT NULL
);

INSERT INTO p_with_re_types (
 P_NO
,ITEM_COUNT$re_Test_Integer
,LOCATION$re_Test_Point
,[S${"Relation":{"S":"string"}}]
) SELECT
  'P1'
  ,5
  ,'{"x":10,"y":12}'
  ,'[{"S": "S1"}, {"S":"S2"}, {"S":"S3"}]'
UNION SELECT
  'P2'
  ,6
  ,'{"x":20,"y":15}'
  ,'[{"S": "S1"}, {"S":"S2"}]';

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[p_with_re_types_dec_at_re]') AND type in (N'U'))
DROP TABLE p_with_re_types_dec_at_re;

CREATE TABLE p_with_re_types_dec_at_re (
  P_NO varchar(20) NOT NULL,
  ITEM_COUNT INT NOT NULL,
  LOCATION VARCHAR(1000) NOT NULL,
  S VARCHAR(MAX) NOT NULL
);

INSERT INTO p_with_re_types_dec_at_re (
 P_NO
,ITEM_COUNT
,LOCATION
,S
)
SELECT
  'P1'
  ,5
  ,'{"x":10,"y":12}'
  ,'[{"S": "S1"}, {"S":"S2"}, {"S":"S3"}]'
UNION SELECT
  'P2'
  ,6
  ,'{"x":20,"y":15}'
  ,'[{"S": "S1"}, {"S":"S2"}]';
